import pymysql
from..config.database_config import config


# 根据学号查询学生信息
def query_student_by_id(student_id):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "SELECT * FROM students WHERE student_id = %s"
        cursor.execute(sql, (student_id,))
        result = cursor.fetchone()
        if result:
            from..models.student_model import Student
            student = Student(*result)
            return student
        connection.close()
    except pymysql.Error as e:
        print(f"查询学生信息时出错: {e}")
    return None


# 增加学生信息
def insert_student(student):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "INSERT INTO students (student_id, name, age, grade) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql, (student.student_id, student.name, student.age, student.grade))
        connection.commit()
        connection.close()
        print("学生信息插入成功")
    except pymysql.Error as e:
        print(f"插入学生信息时出错: {e}")


# 根据学号删除学生信息
def delete_student_by_id(student_id):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "DELETE FROM students WHERE student_id = %s"
        cursor.execute(sql, (student_id,))
        connection.commit()
        connection.close()
        print("学生信息删除成功")
    except pymysql.Error as e:
        print(f"删除学生信息时出错: {e}")


# 根据姓名查询学生信息（简单示例，可根据实际需求优化查询逻辑）
def query_students_by_name(name):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "SELECT * FROM students WHERE name LIKE %s"
        cursor.execute(sql, (f"%{name}%",))
        results = cursor.fetchall()
        students = []
        from..models.student_model import Student
        for result in results:
            student = Student(*result)
            students.append(student)
        connection.close()
        return students
    except pymysql.Error as e:
        print(f"查询学生信息时出错: {e}")
    return []